﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Forms;
using System.Xml;
using MessageBox = Xceed.Wpf.Toolkit.MessageBox;

namespace Gao.Poker.App.Helper
{
    public class ExcelHelper
    {
        public void ExportExcel()
        {
            //Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();


        }

        /// <summary>
        /// 生成Excel文件
        /// </summary>
        /// <param name="DS"></param>
        /// <param name="fileName">文件名</param>
        public static void CreateXmlExcelFile(DataTable[] dt, string fileName)
        {
            #region 指定保存位置

            SaveFileDialog SFD = new SaveFileDialog();
            SFD.InitialDirectory = "C://";
            SFD.FileName = fileName;
            SFD.Filter = "EXCEL文件|*.xls";
            SFD.RestoreDirectory = true;
            SFD.FilterIndex = 1;

            #endregion

            if (SFD.ShowDialog() == DialogResult.OK)
            {
                string foldName = SFD.FileName.ToString();

                #region 同名文件删除
                FileInfo FI = new FileInfo(foldName);

                if (FI.Exists)
                {
                    try
                    {
                        FI.Delete();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "错误消息",MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }
                   
                }
                #endregion

                #region 创建XML对象，二维数组写入并保存

                //创建一个XML文档
                XmlText xmltxt;
                XmlDocument xmldoc = new XmlDocument();
                //XmlNode xmldc = xmldoc.CreateNode(XmlNodeType.XmlDeclaration, "", "");
                XmlDeclaration xmldc = xmldoc.CreateXmlDeclaration("1.0", "UTF-8", "");

                xmldoc.AppendChild(xmldc);

                XmlProcessingInstruction xmlpi = xmldoc.CreateProcessingInstruction("mso-application", "progid=/Excel.Sheet/");
                xmldoc.AppendChild(xmlpi);


                //创建一个工作簿
                XmlElement xmlwb = xmldoc.CreateElement("Workbook");
                xmlwb.SetAttribute("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
                xmlwb.SetAttribute("xmlns:o", "urn:schemas-microsoft-com:office:office");
                xmlwb.SetAttribute("xmlns:x", "urn:schemas-microsoft-com:office:excel");
                xmlwb.SetAttribute("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
                xmlwb.SetAttribute("xmlns:html", "http://www.w3.org/TR/REC-html40");

                xmldoc.AppendChild(xmlwb);

                XmlElement xmldp = xmldoc.CreateElement("DocumentProperties");
                xmldp.SetAttribute("xmlns", "urn:schemas-microsoft-com:office:office");
                XmlElement xmlvs = xmldoc.CreateElement("Version");
                xmltxt = xmldoc.CreateTextNode("11.5606");
                xmlvs.AppendChild(xmltxt);
                xmldp.AppendChild(xmlvs);
                xmlwb.AppendChild(xmldp);

                XmlElement xmlewb = xmldoc.CreateElement("ExcelWorkbook");
                xmlewb.SetAttribute("xmlns", "urn:schemas-microsoft-com:office:excel");
                XmlElement xmlele = xmldoc.CreateElement("WindowHeight");
                xmltxt = xmldoc.CreateTextNode("10005");
                xmlele.AppendChild(xmltxt);
                xmlewb.AppendChild(xmlele);

                xmlele = xmldoc.CreateElement("WindowWidth");
                xmltxt = xmldoc.CreateTextNode("10005");
                xmlele.AppendChild(xmltxt);
                xmlewb.AppendChild(xmlele);

                xmlele = xmldoc.CreateElement("WindowTopX");
                xmltxt = xmldoc.CreateTextNode("120");
                xmlele.AppendChild(xmltxt);
                xmlewb.AppendChild(xmlele);

                xmlele = xmldoc.CreateElement("WindowTopY");
                xmltxt = xmldoc.CreateTextNode("135");
                xmlele.AppendChild(xmltxt);
                xmlewb.AppendChild(xmlele);

                xmlele = xmldoc.CreateElement("ProtectStructure");
                xmltxt = xmldoc.CreateTextNode("False");
                xmlele.AppendChild(xmltxt);
                xmlewb.AppendChild(xmlele);

                xmlele = xmldoc.CreateElement("ProtectWindows");
                xmltxt = xmldoc.CreateTextNode("False");
                xmlele.AppendChild(xmltxt);
                xmlewb.AppendChild(xmlele);

                xmlwb.AppendChild(xmlewb);

                XmlElement xmlsts = xmldoc.CreateElement("Styles");
                xmlwb.AppendChild(xmlsts);

                XmlElement xmlst = xmldoc.CreateElement("Style");
                XmlAttribute xmlarr = xmldoc.CreateAttribute("ss:ID", "urn:schemas-microsoft-com:office:spreadsheet");
                xmlarr.Value = "s21";
                xmlst.Attributes.Append(xmlarr);
                xmlsts.AppendChild(xmlst);

                XmlElement xmlnf = xmldoc.CreateElement("NumberFormat");
                xmlarr = xmldoc.CreateAttribute("ss:Format", "urn:schemas-microsoft-com:office:spreadsheet");
                xmlarr.Value = "General Date";
                xmlnf.Attributes.Append(xmlarr);
                xmlst.AppendChild(xmlnf);

                foreach (DataTable DT in dt)
                {
                    XmlElement xmlws = xmldoc.CreateElement("Worksheet");
                    xmlarr = xmldoc.CreateAttribute("ss:Name", "urn:schemas-microsoft-com:office:spreadsheet");
                    xmlarr.Value = DT.TableName;
                    xmlws.Attributes.Append(xmlarr);
                    xmlwb.AppendChild(xmlws);

                    XmlElement xmltb = xmldoc.CreateElement("Table");
                    xmlws.AppendChild(xmltb);

                    XmlElement xmlrw = xmldoc.CreateElement("Row");
                    xmltb.AppendChild(xmlrw);
                    foreach (DataColumn DC in DT.Columns)
                    {
                        XmlElement xmlcl = xmldoc.CreateElement("Cell");
                        xmlrw.AppendChild(xmlcl);

                        XmlElement xmldt = xmldoc.CreateElement("Data");
                        xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                        xmlarr.Value = "String";
                        xmldt.Attributes.Append(xmlarr);

                        xmltxt = xmldoc.CreateTextNode(DC.ColumnName);
                        xmldt.AppendChild(xmltxt);

                        xmlcl.AppendChild(xmldt);
                    }

                    foreach (DataRow DR in DT.Rows)
                    {
                        xmlrw = xmldoc.CreateElement("Row");
                        xmltb.AppendChild(xmlrw);
                        foreach (DataColumn DC in DT.Columns)
                        {
                            XmlElement xmlcl = xmldoc.CreateElement("Cell");
                            XmlElement xmldt = xmldoc.CreateElement("Data");

                            if (DR[DC.ColumnName] == DBNull.Value)
                            {
                                xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                                xmlarr.Value = "String";
                                xmldt.Attributes.Append(xmlarr);

                                xmltxt = xmldoc.CreateTextNode("");
                            }
                            else
                            {
                                switch (DC.DataType.Name)
                                {
                                    case "Int32":
                                    case "Decimal":
                                    case "Double":

                                        xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                                        xmlarr.Value = "Number";
                                        xmldt.Attributes.Append(xmlarr);

                                        xmltxt = xmldoc.CreateTextNode(DR[DC.ColumnName].ToString().Trim());
                                        break;
                                    case "DateTime":

                                        xmlarr = xmldoc.CreateAttribute("ss:StyleID", "urn:schemas-microsoft-com:office:spreadsheet");
                                        xmlarr.Value = "s21";
                                        xmlcl.Attributes.Append(xmlarr);

                                        xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                                        xmlarr.Value = "DateTime";
                                        xmldt.Attributes.Append(xmlarr);

                                        xmltxt = xmldoc.CreateTextNode(Convert.ToDateTime(DR[DC.ColumnName]).ToString("yyyy-MM-ddTHH:mm:ss").Trim());

                                        break;
                                    default:

                                        xmlarr = xmldoc.CreateAttribute("ss:Type", "urn:schemas-microsoft-com:office:spreadsheet");
                                        xmlarr.Value = "String";
                                        xmldt.Attributes.Append(xmlarr);

                                        xmltxt = xmldoc.CreateTextNode(DR[DC.ColumnName].ToString().Trim());
                                        break;
                                }
                            }

                            xmldt.AppendChild(xmltxt);

                            xmlcl.AppendChild(xmldt);
                            xmlrw.AppendChild(xmlcl);
                        }
                    }

                    XmlElement xmlwso = xmldoc.CreateElement("WorksheetOptions");
                    xmlwso.SetAttribute("xmlns", "urn:schemas-microsoft-com:office:excel");

                    xmlele = xmldoc.CreateElement("Unsynced");
                    xmlwso.AppendChild(xmlele);

                    xmlele = xmldoc.CreateElement("ProtectObjects");
                    xmltxt = xmldoc.CreateTextNode("False");
                    xmlele.AppendChild(xmltxt);
                    xmlwso.AppendChild(xmlele);

                    xmlele = xmldoc.CreateElement("ProtectScenarios");
                    xmltxt = xmldoc.CreateTextNode("False");
                    xmlele.AppendChild(xmltxt);
                    xmlwso.AppendChild(xmlele);

                    xmlws.AppendChild(xmlwso);
                }

                StreamWriter sw = new StreamWriter(foldName, false, System.Text.Encoding.UTF8);

                xmldoc.Save(sw);

                sw.Dispose();

                if (MessageBox.Show("导出成功,是否要打开此文件?", "信息提示", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
                {
                    System.Diagnostics.Process.Start(SFD.FileName);
                }

                #endregion
            }
        }
    }
}
